package groups.invertebrates.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import groups.invertebrates.InvertebrateSpecies;

public class JDBCInvertebrateSpeciesDao implements IInvertebrateSpeciesDao{

	private Connection con;

	public JDBCInvertebrateSpeciesDao(Connection con){
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.con = con;
	}

	public void create(String name, String size, String picture, int includeeIdGenus, int hasIdBody, int livesIdHabitat, int invEtiologicalAgent){
		InvertebrateSpecies invertebrateSpecies = new InvertebrateSpecies();
		invertebrateSpecies.setName(name);
		invertebrateSpecies.setSize(size);
		invertebrateSpecies.setPicture(picture);
		invertebrateSpecies.setIncludeeIdGenus(includeeIdGenus);
		invertebrateSpecies.setHasIdBody(hasIdBody);
		invertebrateSpecies.setLivesIdHabitat(livesIdHabitat);
		invertebrateSpecies.setInvEtiologicalAgent(invEtiologicalAgent);
		
		insert(invertebrateSpecies);
	}

	public void delete(int idInvertebrateSpecies) {		
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("DELETE FROM APP.INVERTEBRATESPECIES WHERE IDINVERTEBRATESPECIES = " + idInvertebrateSpecies);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public InvertebrateSpecies read(int idInvertebrateSpecies) {
		InvertebrateSpecies invertebrateSpecies= new InvertebrateSpecies();
		try {
			ResultSet result;
			Statement stmt = con.createStatement();
			result = stmt.executeQuery("SELECT * FROM APP.INVERTEBRATESPECIES WHERE IDINVERTEBRATESPECIES = " + idInvertebrateSpecies);
			invertebrateSpecies.setIdInvertebrateSpecies(idInvertebrateSpecies);
			result.next();
			invertebrateSpecies.setName(result.getString("NAME"));
			invertebrateSpecies.setSize(result.getString("SIZE"));
			invertebrateSpecies.setPicture(result.getString("PICTURE"));
			invertebrateSpecies.setIncludeeIdGenus(result.getInt("INCLUDEEGENUS"));
			invertebrateSpecies.setHasIdBody(result.getInt("HASIDBODY"));
			invertebrateSpecies.setLivesIdHabitat(result.getInt("LIVESIDHABITAT"));
			invertebrateSpecies.setInvEtiologicalAgent(result.getInt("INVETIOLOGICALAGENT"));
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return invertebrateSpecies;
	}

	public Vector<InvertebrateSpecies> readAll() {
		Vector<InvertebrateSpecies> invertebrateSpeciesVector = new Vector<InvertebrateSpecies>();
		try {
			ResultSet result;
			Statement stmt = con.createStatement();
			result = stmt.executeQuery("SELECT * FROM APP.INVERTEBRATESPECIES");
					while (result.next()){
						InvertebrateSpecies invertebrateSpecies = new InvertebrateSpecies();
						invertebrateSpecies.setIdInvertebrateSpecies(result.getInt("IDINVERTEBRATESPECIES"));
						invertebrateSpecies.setName(result.getString("NAME"));
						invertebrateSpecies.setSize(result.getString("SIZE"));
						invertebrateSpecies.setPicture(result.getString("PICTURE"));
						invertebrateSpecies.setIncludeeIdGenus(result.getInt("INCLUDEEGENUS"));
						invertebrateSpecies.setHasIdBody(result.getInt("HASIDBODY"));
						invertebrateSpecies.setLivesIdHabitat(result.getInt("LIVESIDHABITAT"));
						invertebrateSpecies.setInvEtiologicalAgent(result.getInt("INVETIOLOGICALAGENT"));
						
						invertebrateSpeciesVector.add(invertebrateSpecies);
					}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return invertebrateSpeciesVector;

	}

	public void update(int idInvertebrateSpecies, String name, String size, String picture, int includeeIdGenus, int hasIdBody, int livesIdHabitat, int invEtiologicalAgent){
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("UPDATE APP.INVERTEBRATESPECIES " +
					"SET NAME='"+ name + "', SIZE='" + size + "', PICTUR='" + picture + "', INCLUDEEIDGENUS='" + String.valueOf(includeeIdGenus) + "', HASIDBODY ='" + String.valueOf(hasIdBody) + "', LIVESIDHABITAT='" + String.valueOf(livesIdHabitat) + "', INVETIOLOGICALAGENT='" + String.valueOf(invEtiologicalAgent) + "' WHERE IDINVERTEBRATESPECIES=" + idInvertebrateSpecies);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void insert(InvertebrateSpecies invertebrateSpecies){
		try{
			int idInvertebrateSpecies;
			ResultSet result;
			Statement stmt = con.createStatement();
			result = stmt.executeQuery("SELECT MAX(IDINVERTEBRATESPECIES) AS IDINVERTEBRATESPECIES FROM APP.INVERTEBRATESPECIES");
			System.out.println(result.getCursorName());
			result.next();//Cursor na posicao da primeira tupla
			idInvertebrateSpecies = result.getInt("IDINVERTEBRATESPECIES") + 1;
			stmt.executeUpdate("INSERT INTO APP.INVERTEBRATESPECIES VALUES (" +
					idInvertebrateSpecies + ", '" + invertebrateSpecies.getName() + "' , '" + invertebrateSpecies.getSize() + "' , '" + invertebrateSpecies.getPicture() + "' , '" + invertebrateSpecies.getIncludeeIdGenuss() + "' , '"+ invertebrateSpecies.getHasIdBody() + "' , '" + invertebrateSpecies.getLivesIdHabitat() + "' , '" + invertebrateSpecies.getInvEtiologicalAgent() + "')");
		}
		catch(SQLException e){
			e.printStackTrace();
		}

	}

}
